package com.bw.dao;

import com.bw.doman.sugar;
import com.bw.doman.sugarTable;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface sugarDao {
    @Select("SELECT sum(amount) FROM order_info WHERE create_time>='2023-01-06 00:00:00' AND create_time<='2023-01-06 23:59:59'")
    double getList();

    @Select("CALL je")
    List<sugar> getList2();

    @Select("SELECT a.*,rank() over(PARTITION by a.name ORDER BY a.distance) kc FROM\n" +
            "(SELECT b.org_name name,\n" +
            "sum(t.actual_distance) distance,\n" +
            "count(b.org_name) num,\n" +
            "sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time)) time,\n" +
            "(sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time))/count(b.org_name)) avgTime,\n" +
            "(sum(t.actual_distance)/count(b.org_name)) avgDistance FROM transport_task t \n" +
            "LEFT JOIN line_base_info l on t.line_id=l.id\n" +
            "LEFT JOIN base_organ b on l.org_id=b.id\n" +
            "GROUP BY b.org_name)a")
    List<sugarTable> getList3();


    @Select("SELECT b2.name,count(b2.name) value FROM transport_task_detail t \n" +
            "LEFT JOIN transport_task t1 on t.transport_task_id=t1.id\n" +
            "LEFT JOIN line_base_info l on t1.line_id=l.id\n" +
            "LEFT JOIN base_organ b on l.org_id=b.id\n" +
            "LEFT JOIN base_region_info b1 on b.region_id=b1.id\n" +
            "LEFT JOIN base_region_info b2 on b1.parent_id=b2.id\n" +
            "GROUP BY b2.name")
    List<sugar> getList4();

    @Select("SELECT count(*) value FROM transport_task  WHERE create_time>='2023-01-06 00:00:00' AND create_time<'2023-01-06 23:59:59'\n")
    double getList5();


    @Select("SELECT b.name,count(b.name) value FROM order_info o \n" +
            "LEFT JOIN order_cargo c on c.order_id=o.id \n" +
            "LEFT JOIN base_dic b on c.cargo_type=b.id\n" +
            "GROUP BY b.name")
    List<sugar> getList6();


    @Select("SELECT i1.name,count(i1.name) value FROM express_task_delivery t \n" +
            "LEFT JOIN base_organ b on t.org_id=b.id \n" +
            "LEFT JOIN base_region_info i on b.region_id=i.id\n" +
            "LEFT JOIN base_region_info i1 on i.parent_id=i1.id\n" +
            "GROUP BY i1.name\n")
    List<sugar> getList7();


    @Select("CALL jd")
    List<sugar> getList8();


    @Select("SELECT '完成运输次数' name, count(*) value FROM transport_task WHERE create_time>='2023-01-06 00:00:00' AND create_time<'2023-01-06 23:59:59'\n" +
            "UNION\n" +
            "SELECT '完成运输里程' name,sum(actual_distance) value FROM transport_task WHERE create_time>='2023-01-06 00:00:00' AND create_time<'2023-01-06 23:59:59'\n" +
            "UNION\n" +
            "SELECT '完成运输时长' name,sum(TIMESTAMPDIFF(second,actual_start_time,actual_end_time)) value FROM transport_task WHERE create_time>='2023-01-06 00:00:00' AND create_time<'2023-01-06 23:59:59'")
    List<sugar> getList9();


    @Select("SELECT b.org_name name,count(*) value FROM order_org_bound o \n" +
            "LEFT JOIN  base_organ b on o.org_id=b.id\n" +
            "WHERE status=64004\n" +
            "GROUP BY b.org_name")
    List<sugar> getList10();
}
